package com.websuites.esb.plugins.proc;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.sql.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.math.BigDecimal;

import javax.servlet.http.HttpSession;
import javax.sql.DataSource;

import org.springframework.jdbc.datasource.DataSourceUtils;

import com.websuites.core.Constant;
import com.websuites.core.request.WsHttpServletRequest;
import com.websuites.core.response.IResult;
import com.websuites.core.response.WsHttpServletResponse;
import com.websuites.esb.repository.Bexs;
import com.websuites.esb.repository.Param;
import com.websuites.utils.LogUtil;
import com.websuites.utils.SQLUtil;

@SuppressWarnings("unchecked")
public class ORAProc implements IProc
{
	private DataSource dataSource;
	private Bexs bexs; 
	private String sLoginInfo = Constant.WS_KCBP_LOGIN_INFO;
	private String sSchema;
		
	public DataSource getDataSource()
	{
		return dataSource;
	}

	public void setDataSource(DataSource dataSource)
	{
		this.dataSource = dataSource;
	}

	public Bexs getBexs()
	{
		return bexs;
	}

	public void setBexs(Bexs bexs)
	{
		this.bexs = bexs;
	}

	public String getLoginInfo()
	{
		return sLoginInfo;
	}

	public void setLoginInfo(String loginInfo)
	{
		this.sLoginInfo = loginInfo;
	}

	public String getSchema()
	{
		return sSchema;
	}

	public void setSchema(String sSchema)
	{
		this.sSchema = sSchema;
	}

	public void doProc(WsHttpServletRequest request, WsHttpServletResponse response, HashMap mapParam, IResult rs) throws SQLException
	{
		String sBex = (String)mapParam.get(Constant.WS_BEX_NO);

		Connection oraConn = DataSourceUtils.getConnection(this.dataSource);

		String sSchema = (String)mapParam.get("schema");
		if (sSchema.equals(""))
		{
			sSchema = this.sSchema;
		}
		String sPkgName = (String)mapParam.get("package");;
		String sProcName = (String)mapParam.get("procedure");;
		int iResults = Integer.parseInt((String)mapParam.get("results"));

		HashMap mapLoginInfo = null;
		if (request != null)
		{
			HttpSession session = request.getSession();
			mapLoginInfo = (HashMap)session.getAttribute(sLoginInfo);
			if (mapLoginInfo == null)
			{
				mapLoginInfo = new HashMap();
			}
		}
		else
		{
			mapLoginInfo = new HashMap();
		}	

		String  sSql = "";
		if (sPkgName.equals(""))
		{
			sSql = "{call " + sSchema + "." + sProcName + "(";			
		}
		else
		{
			sSql = "{call " + sSchema + "." + sPkgName + "." + sProcName + "(";
		}

		int iParamCount = bexs.getParamCount(sBex);
		int iTotalParams = iParamCount + iResults + 2 + mapLoginInfo.size();
		for (int i = 0; i < iTotalParams; i++) 
		{
			sSql += "?";
			if (i < iTotalParams - 1) 
			{
				sSql += ",";
			}
		}
		sSql += ")}";

		LogUtil.info(sSql);
		CallableStatement sqlstmt = oraConn.prepareCall(sSql);

		sqlstmt.registerOutParameter(1, Types.INTEGER);
		sqlstmt.registerOutParameter(2, Types.VARCHAR);

		for (int j = 0; j < iResults; j++) 
		{
			sqlstmt.registerOutParameter(2 + j + 1, oracle.jdbc.OracleTypes.CURSOR);
		}

		int k = 2 + iResults + 1;

		Iterator itMap = mapLoginInfo.entrySet().iterator();
		while (itMap.hasNext())
		{
			Map.Entry element = (Map.Entry)itMap.next();
			Object oParam = element.getValue();
			if (oParam instanceof Integer)
			{
				int iValue = ((Integer)oParam).intValue();
				sqlstmt.setInt(k++, iValue);
			}
			else
			{
				sqlstmt.setString(k++, oParam.toString());
			}
		}
			
		Iterator itIn = bexs.getParamIterator(sBex);
		while (itIn.hasNext()) 
		{
			Param param = (Param)itIn.next();
			String sName = param.getName();
			String sType = param.getType();

			if (sType.equals("integer")) 
			{
				Object oParam = mapParam.get(sName);
				if (oParam != null)
				{
					int iValue = ((Integer)oParam).intValue();
					sqlstmt.setInt(k++, iValue);
				}
				else
				{
					sqlstmt.setNull(k++, java.sql.Types.INTEGER);
				}
			}
			else if (sType.equals("number")) 
			{
				Object oParam = mapParam.get(sName);
				if (oParam != null)
				{
					BigDecimal iValue = ((BigDecimal)oParam);
					sqlstmt.setBigDecimal(k++, iValue);
				}
				else
				{
					sqlstmt.setNull(k++, java.sql.Types.DECIMAL);
				}
			} 
			else if (sType.equals("numeric")) 
			{
				Object oParam = mapParam.get(sName);
				if (oParam != null)
				{
					BigDecimal iValue = ((BigDecimal)oParam);
					sqlstmt.setBigDecimal(k++, iValue);
				}
				else
				{
					sqlstmt.setNull(k++, java.sql.Types.NUMERIC);
				}
			} 
			else if (sType.equals("varchar")) 
			{
				Object oParam = mapParam.get(sName);
				if (oParam != null)
				{
					sqlstmt.setString(k++, oParam.toString());
				}
				else
				{
					sqlstmt.setNull(k++, java.sql.Types.VARCHAR);
				}
			} 
			else if (sType.equals("varchar2")) 
			{
				Object oParam = mapParam.get(sName);
				if (oParam != null)
				{
					sqlstmt.setString(k++, oParam.toString());
				}
				else
				{
					sqlstmt.setNull(k++, java.sql.Types.VARCHAR);
				}
			} 
			else if (sType.equals("character")) 
			{
				Object oParam = mapParam.get(sName);
				if (oParam != null)
				{
					sqlstmt.setString(k++, oParam.toString());
				}
				else
				{
					sqlstmt.setNull(k++, java.sql.Types.CHAR);
				}
			} 
			else if (sType.equals("sqldate")) 
			{
				Object oParam = mapParam.get(sName);
				if (oParam != null)
				{
					sqlstmt.setDate(k++, (Date)oParam);
				}
				else
				{
					sqlstmt.setNull(k++, java.sql.Types.DATE);
				}
			} 
			else 
			{
				DataSourceUtils.releaseConnection(oraConn, this.dataSource);

				String sError = "Unsupported argument types " + sType + ". Please check bex configuration file";
				LogUtil.error(sError);
				throw new SQLException(sError);
			}
		}

		sqlstmt.execute();

		if (iResults == 1) 
		{
			int iErrorCode = sqlstmt.getInt(1);
			String sErrorMsg = sqlstmt.getString(2);

			rs.setErrorCode(Integer.toString(iErrorCode));
			rs.setErrorMessage(sErrorMsg != null ? sErrorMsg : "");

			if (iErrorCode == 1) 
			{
				ResultSet rsSql = (ResultSet) sqlstmt.getObject(3);
				List list = SQLUtil.ResultSetToList(rsSql, rs);

				rs.setResType(Constant.WS_TYPE_LISTMAP);
				rs.setResult(list);

				rsSql.close();
			}

			sqlstmt.close();
		} 
		else if (iResults > 1) 
		{
			int iErrorCode = sqlstmt.getInt(1);
			String sErrorMsg = sqlstmt.getString(2);

			rs.setErrorCode(Integer.toString(iErrorCode));
			rs.setErrorMessage(sErrorMsg != null ? sErrorMsg : "");

			if (iErrorCode == 1) 
			{
				List list = SQLUtil.MulResultSetToList(sqlstmt, rs, iResults, 2);

				rs.setResType(Constant.WS_TYPE_MULLIST);
				rs.setResult(list);
			}

			sqlstmt.close();
		} 
		else 
		{
			int iErrorCode = sqlstmt.getInt(1);
			String sErrorMsg = sqlstmt.getString(2);

			rs.setErrorCode(Integer.toString(iErrorCode));
			rs.setErrorMessage(sErrorMsg != null ? sErrorMsg : "");
			
			sqlstmt.close();
		}
		DataSourceUtils.releaseConnection(oraConn, this.dataSource);
	}
}
